#!/usr/bin/env python
# coding=utf-8
from tkinter import *
import pymysql
from tkinter import messagebox
from tkinter.ttk import Combobox

# 数据库连接信息
host = "127.0.0.1"
user = ""
password = "" 
dbname = "db_band"

# 连接数据库
def connect_db(str1,str2):
    return pymysql.connect(host=host, user=str1, password=str2, database=dbname)
#-----------------登录------------------
# 检查登录信息
def check_login(kind, e1, e2, w1):
    global band_id, band_name, fan_id, fan_name, admin_id, admin_name
    global user,password
    user=e1.get()
    password=e2.get()
    try:
        db = connect_db(e1.get(),e2.get())
        cur = db.cursor()
        sql = "SELECT * FROM {} WHERE {}=%s AND Pwd=%s"
        cur.execute(sql.format(kind, "BandUsername" if kind == "bandlogin" else ("FanUsername" if kind == "fanlogin" else "AdminUsername")), (e1.get(), e2.get()))
        results = cur.fetchall()
        if results:
            w1.destroy()
            if kind == "bandlogin":
                band_id = results[0][0]
                band_name = results[0][1]
                band_operate()
            elif kind == "fanlogin":
                fan_id = results[0][0]
                fan_name = results[0][1]
                fan_operate()
            elif kind == "adminlogin":
                admin_id = results[0][0]
                admin_name = results[0][1]
                admin_operate()
            else:
                messagebox.showerror("错误", "密码不正确，请重新输入")
    except pymysql.Error as e:
        print("数据查询失败 " + str(e))
    finally:
        db.close()

# 登录界面
def login(str, win):
    win.destroy()
    log_in = Tk()
    log_in.title("登录")
    log_in.geometry("350x200")
    idE = Entry(log_in, width=30)
    pwdE = Entry(log_in, width=30)
    Label(log_in, text=str, font="微软雅黑 14").grid(row=0, column=0, columnspan=2, sticky="w", pady=10)
    Label(log_in, text="用户名", font="微软雅黑 14").grid(row=1, column=0, sticky="w")
    idE.grid(row=1, column=1, sticky="e", padx=40)
    Label(log_in, text="密码", font="微软雅黑 14").grid(row=2, column=0, sticky="w")
    pwdE.grid(row=2, column=1, sticky="e", padx=40)
    Button(log_in, text="登录", font="微软雅黑 10", relief="solid", command=lambda: check_login(str, idE, pwdE, log_in)).grid(row=3, column=0, columnspan=2, pady=20, padx=20)

#-------------乐队操作函数------------
# 更新乐队信息
def update_band_info(v, e):
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "UPDATE bands SET {}=%s WHERE BandID=%s"
        cur.execute(sql.format(v.get()), (e.get(), band_id))
        db.commit()
        messagebox.showinfo("成功", "更新成功")
    except pymysql.Error as e:
        messagebox.showinfo("失败", "更新失败 " + str(e))
        db.rollback()
    finally:
        db.close()

# 获取乐队信息
def get_band_info():
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "SELECT * FROM bands WHERE BandID=%s"
        cur.execute(sql, (band_id,))
        results = cur.fetchall()
        return results
    except pymysql.Error as e:
        print("数据查询失败 " + str(e))
    finally:
        db.close()

# 乐队信息界面
def update_band_window(win):
    root = Toplevel(win)
    root.title("乐队信息")
    root.geometry("800x230")
    Label(root, text="{:<14}{:<14}{:<14}{:<14}{:<14}".format(
        "乐队ID", "乐队名称", "队长ID", "成立年份", "乐队介绍")).grid(row=0, column=0, columnspan=2, pady=9, sticky="w")
    var = StringVar()
    rels = get_band_info()
    cb = Combobox(root, textvariable=var, width=17)
    cb["value"] = ("BandName", "CaptainID", "FormedYear", "BandDescription")
    i = 1
    for rel in rels:
        s1 = "{:<14}{:<14}{:<14}{:<14}{:<14}".format(
            rel[0], rel[1], rel[2], rel[3], rel[4])
        Label(root, text=s1).grid(row=i, column=0, columnspan=2, padx=5, pady=30, sticky="w")
        i += 1
    e1 = Entry(root)
    Label(root, text="请选择将要修改的信息").grid(padx=5, row=i, pady=8, column=0, sticky="e")
    cb.grid(padx=5, row=i, column=1, pady=8, sticky="w")
    i += 1
    Label(root, text="请输入要修改的值").grid(padx=5, row=i, column=0, sticky="e")
    e1.grid(padx=5, row=i, column=1, sticky="w")
    Button(root, text="提交", command=lambda: update_band_info(var, e1)).grid(row=i + 1, column=0, columnspan=2,pady=8)

# 更新乐队成员信息
def update_member_info(v, e, selected_row):
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "UPDATE members SET {}=%s WHERE MemberID=%s"
        cur.execute(sql.format(v.get()), (e.get(), selected_row))
        db.commit()
        messagebox.showinfo("成功", "更新成功")
    except pymysql.Error as e:
        messagebox.showinfo("失败", "更新失败 " + str(e))
        db.rollback()
    finally:
        db.close()

# 获取乐队成员信息
def get_members_info():
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "SELECT * FROM members WHERE BandID=%s"
        cur.execute(sql, (band_id,))
        results = cur.fetchall()
        return results
    except pymysql.Error as e:
        print("数据查询失败 " + str(e))
    finally:
        db.close()

# 乐队成员界面
def update_members_window(win):
    root = Toplevel(win)
    root.title("乐队成员信息")
    root.geometry("600x400")
    Label(root, text="{:<14}{:<14}{:<14}{:<14}{:<14}{:<14}{:<14}".format(
        "成员ID", "姓名", "性别", "年龄", "角色", "加入日期", "离开日期")).grid(row=0, column=0, columnspan=2, pady=9, sticky="w")
    var = StringVar()
    rels = get_members_info()
    cb = Combobox(root, textvariable=var, width=17)
    cb["value"] = ("Name", "Gender", "Age", "Role","JoinDate","LeaveDate")
    i = 1
    for rel in rels:
        s1 = "{:<14}{:<14}{:<14}{:<14}{:<14}{:<14}{:<14}".format(
            rel[0], rel[2], rel[3], rel[4], rel[5],rel[6],rel[7] if rel[7] else "")
        Label(root, text=s1).grid(row=i, column=0, columnspan=2, padx=5, pady=30, sticky="w")
        i += 1

    # 添加选择行的下拉框和输入框
    sel_var = IntVar()
    sel_combobox = Combobox(root, textvariable=sel_var, width=5)
    sel_combobox["values"] = list(range(1, len(rels) + 1))
    sel_combobox.grid(padx=5, row=i, column=0, pady=8, sticky="w")
    i += 1

    e1 = Entry(root)
    Label(root, text="请选择将要修改的信息").grid(padx=5, row=i, pady=8, column=0, sticky="e")
    cb.grid(padx=5, row=i, column=1, pady=8, sticky="w")
    i += 1
    Label(root, text="请输入要修改的值").grid(padx=5, row=i, column=0, sticky="e")
    e1.grid(padx=5, row=i, column=1, sticky="w")
    Button(root, text="提交", command=lambda: update_member_info(var, e1, sel_var.get())).grid(row=i + 1, column=0, columnspan=2,pady=8)

# 更新乐队专辑信息
def update_albums_info(v, e, selected_row):
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "UPDATE albums SET {}=%s WHERE AlbumID=%s"
        cur.execute(sql.format(v.get()), (e.get(), selected_row))
        db.commit()
        messagebox.showinfo("成功", "更新成功")
    except pymysql.Error as e:
        messagebox.showinfo("失败", "更新失败 " + str(e))
        db.rollback()
    finally:
        db.close()

# 获取乐队专辑信息
def get_albums_info():
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "SELECT * FROM albums WHERE BandID=%s"
        cur.execute(sql, (band_id,))
        results = cur.fetchall()
        return results
    except pymysql.Error as e:
        print("数据查询失败 " + str(e))
    finally:
        db.close()

# 乐队专辑界面
def update_albums_window(win):
    root = Toplevel(win)
    root.title("乐队专辑信息")
    root.geometry("600x400")
    Label(root, text="{:<14}{:<14}{:<14}{:<14}".format(
        "专辑ID", "专辑名称", "发布日期", "介绍")).grid(row=0, column=0, columnspan=2, pady=9, sticky="w")
    var = StringVar()
    rels = get_albums_info()
    cb = Combobox(root, textvariable=var, width=17)
    cb["value"] = ("AlbumName", "ReleaseDate", "AlbumDescription")
    i = 1
    for rel in rels:
        s1 = "{:<14}{:<14}{:<14}{:<14}".format(
            rel[0], rel[2], rel[3], rel[4])
        Label(root, text=s1).grid(row=i, column=0, columnspan=2, padx=5, pady=30, sticky="w")
        i += 1

    # 添加选择行的下拉框和输入框
    sel_var = IntVar()
    sel_combobox = Combobox(root, textvariable=sel_var, width=5)
    sel_combobox["values"] = list(range(1, len(rels) + 1))
    sel_combobox.grid(padx=5, row=i, column=0, pady=8, sticky="w")
    i += 1

    e1 = Entry(root)
    Label(root, text="请选择将要修改的信息").grid(padx=5, row=i, pady=8, column=0, sticky="e")
    cb.grid(padx=5, row=i, column=1, pady=8, sticky="w")
    i += 1
    Label(root, text="请输入要修改的值").grid(padx=5, row=i, column=0, sticky="e")
    e1.grid(padx=5, row=i, column=1, sticky="w")
    Button(root, text="提交", command=lambda: update_albums_info(var, e1, sel_var.get())).grid(row=i + 1, column=0, columnspan=2,pady=8)

# 更新乐队演唱会信息
def update_concerts_info(v, e, selected_row):
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "UPDATE concerts SET {}=%s WHERE ConcertID=%s"
        cur.execute(sql.format(v.get()), (e.get(), selected_row))
        db.commit()
        messagebox.showinfo("成功", "更新成功")
    except pymysql.Error as e:
        messagebox.showinfo("失败", "更新失败 " + str(e))
        db.rollback()
    finally:
        db.close()

# 获取乐队演唱会信息
def get_concerts_info():
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "SELECT * FROM concerts WHERE BandID=%s"
        cur.execute(sql, (band_id,))
        results = cur.fetchall()
        return results
    except pymysql.Error as e:
        print("数据查询失败 " + str(e))
    finally:
        db.close()

# 乐队演唱会界面
def update_concerts_window(win):
    root = Toplevel(win)
    root.title("乐队成员信息")
    root.geometry("600x400")
    Label(root, text="{:<14}{:<14}{:<14}{:<14}".format(
        "演唱会ID", "演唱会名称", "举办日期", "地点")).grid(row=0, column=0, columnspan=2, pady=9, sticky="w")
    var = StringVar()
    rels = get_concerts_info()
    cb = Combobox(root, textvariable=var, width=17)
    cb["value"] = ("ConcertName", "Date", "Location")
    i = 1
    for rel in rels:
        s1 = "{:<14}{:<14}{:<14}{:<14}".format(
            rel[0], rel[2], rel[3], rel[4])
        Label(root, text=s1).grid(row=i, column=0, columnspan=2, padx=5, pady=30, sticky="w")
        i += 1

    # 添加选择行的下拉框和输入框
    sel_var = IntVar()
    sel_combobox = Combobox(root, textvariable=sel_var, width=5)
    sel_combobox["values"] = list(range(1, len(rels) + 1))
    sel_combobox.grid(padx=5, row=i, column=0, pady=8, sticky="w")
    i += 1

    e1 = Entry(root)
    Label(root, text="请选择将要修改的信息").grid(padx=5, row=i, pady=8, column=0, sticky="e")
    cb.grid(padx=5, row=i, column=1, pady=8, sticky="w")
    i += 1
    Label(root, text="请输入要修改的值").grid(padx=5, row=i, column=0, sticky="e")
    e1.grid(padx=5, row=i, column=1, sticky="w")
    Button(root, text="提交", command=lambda: update_concerts_info(var, e1, sel_var.get())).grid(row=i + 1, column=0, columnspan=2,pady=8)

# 获取乐队对应的歌迷信息
def get_band_fans_info():
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "SELECT fans.* FROM fans JOIN fanbands ON fans.FanID=fanbands.FanID WHERE fanbands.BandID=%s"
        cur.execute(sql, (band_id,))
        results = cur.fetchall()
        return results
    except pymysql.Error as e:
        print(" 数据查询失败 " + str(e))
    finally:
        db.close()

# 显示乐队歌迷信息
def show_fans_window(win):
    root = Toplevel(win)
    root.title(" 乐队歌迷信息 ")
    root.geometry("600x500")
    
    # 设置表格头部
    Label(root, text=" 歌迷 ID       姓名          性别      年龄      职业      学历 ").grid(row=0, column=0, columnspan=2, padx=5, pady=9, sticky="w")

    # 获取数据并显示
    rels = get_band_fans_info()
    i = 1
    for rel in rels:
        s1 = "{:<14}{:<14}{:<14}{:<14}{:<14}{:<14}".format(rel[0], rel[1], rel[2], rel[3], rel[4], rel[5])
        Label(root, text=s1).grid(row=i, column=0, columnspan=2, padx=5, pady=9, sticky="w")
        i += 1

# 获取乐队专辑乐评信息
def get_reviews_info():
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "SELECT reviews.*,fans.Name,albums.AlbumName FROM reviews JOIN albums ON reviews.AlbumID=albums.AlbumID JOIN fans ON fans.FanID=reviews.FanID WHERE albums.BandID=%s"
        cur.execute(sql, (band_id,))
        results = cur.fetchall()
        return results
    except pymysql.Error as e:
        print("数据查询失败 " + str(e))
    finally:
        db.close()

# 显示乐队专辑乐评信息
def show_reviews_window(win):
    root = Toplevel(win)
    root.title("乐队专辑乐评信息")
    root.geometry("600x500")
    Label(root, text="乐评ID         专辑名           乐迷名          评论                                            评分").grid(row=0, column=0, sticky="w", columnspan=2, padx=5, pady=9)
    rels = get_reviews_info()
    i = 1
    for rel in rels:
        s1 = "{:<14}{:<14}{:<14}{:<20}{:<14}".format(
            rel[0], rel[6], rel[5], rel[3], rel[4])
        Label(root, text=s1).grid(row=i, column=0, columnspan=2, padx=5, pady=9, sticky="w")
        i += 1

#-------------歌迷操作函数------------
def update_fan_info(v, e):
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "UPDATE fans SET {}=%s WHERE FanID=%s"
        cur.execute(sql.format(v.get()), (e.get(), fan_id))
        db.commit()
        messagebox.showinfo("成功", "更新成功")
    except pymysql.Error as e:
        messagebox.showinfo("失败", "更新失败" + str(e))
        db.rollback()
    finally:
        db.close()

def get_fan_info():
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "SELECT * FROM fans WHERE FanID=%s"
        cur.execute(sql, (fan_id,))
        results = cur.fetchall()
        return results
    except pymysql.Error as e:
        print("数据查询失败" + str(e))
    finally:
        db.close()

def update_fan_window(win):
    root = Toplevel(win)
    root.title("乐迷信息")
    root.geometry("800x230")

    # 设置表格头部
    Label(root, text="{:<14}{:<14}{:<14}{:<14}{:<14}".format(
        "乐迷 ID", "姓名", "性别", "年龄", "职业", "学历")).grid(row=0, column=0, columnspan=2, pady=9, sticky="w")

    var = StringVar()
    rels = get_fan_info()

    # 显示现有信息
    cb = Combobox(root, textvariable=var, width=17)
    cb["value"] = ("Name", "Gender", "Age", "Occupation", "Education")
    
    i = 1
    for rel in rels:
        s1 = "{:<14}{:<14}{:<14}{:<14}{:<14}{:<14}".format(
            rel[0], rel[1], rel[2], rel[3], rel[4], rel[5])
        Label(root, text=s1).grid(row=i, column=0, columnspan=2, padx=5, pady=30, sticky="w")
        i += 1

    e1 = Entry(root)

    # 选择要修改的信息
    Label(root, text="请选择将要修改的信息").grid(padx=5, row=i, pady=8, column=0, sticky="e")
    cb.grid(padx=5, row=i, column=1, pady=8, sticky="w")
    i += 1

    # 输入新的值
    Label(root, text="请输入要修改的值").grid(padx=5, row=i, column=0, sticky="e")
    e1.grid(padx=5, row=i, column=1, sticky="w")
    i += 1

    # 提交按钮
    Button(root, text="提交", command=lambda: update_fan_info(var, e1)).grid(row=i + 1, column=0, columnspan=2, pady=8)

#获取歌迷喜欢的乐队
def get_fanbands_info():
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "SELECT bands.* FROM bands JOIN fanbands ON bands.BandID=fanbands.BandID WHERE fanbands.FanID=%s"
        cur.execute(sql, (fan_id,))
        results = cur.fetchall()
        return results
    except pymysql.Error as e:
        print("数据查询失败 " + str(e))
    finally:
        db.close()

#增加喜欢的乐队
def add_favorite_band(parent_window, fan_id):  # 注意：这里需要传入 fan_id 参数
    def submit():
        selected_band_id = band_id_var.get()
        if not selected_band_id.strip():  # 检查是否选择了有效的乐队 ID
            messagebox.showwarning("警告", "请选择一个乐队")
            return
        
        try:
            db = connect_db(user,password)
            cur = db.cursor()
            # 检查是否存在重复关系
            cur.execute("""
                SELECT COUNT(*) FROM fanbands 
                WHERE FanID=%s AND BandID=%s
            """, (fan_id, selected_band_id))
            count = cur.fetchone()[0]
            if count > 0:
                messagebox.showwarning("提示", "已经存在该条喜欢的乐队记录")
                return
            
            # 插入新记录到 fanbands 表
            cur.execute("""
                INSERT INTO fanbands (FanID, BandID) VALUES (%s, %s)
            """, (fan_id, selected_band_id))
            db.commit()
            messagebox.showinfo("成功", "添加成功")
            popup.destroy()
            refresh_fanbands_listbox()  # 刷新列表框内容
        except pymysql.Error as e:
            messagebox.showerror("错误", f"添加失败: {str(e)}")
            db.rollback()
        finally:
            db.close()

    # 创建弹出窗口
    popup = Toplevel(parent_window)
    popup.title("增加喜欢的乐队")
    popup.geometry("400x200")
    Label(popup, text="请选择您想添加的乐队").pack(pady=10)
    band_id_var = StringVar()
    # 直接获取所有乐队的 BandID 并用于组合框的选项
    all_band_ids = [str(band[0]) for band in get_all_bands()]
    band_combobox = Combobox(popup, values=all_band_ids, textvariable=band_id_var)
    band_combobox.pack(pady=10)
    btn_submit = Button(popup, text="提交", command=submit)
    btn_submit.pack(pady=10)
    popup.wait_window()  # 等待窗口关闭

#删除喜欢的乐队
def delete_favorite_band(listbox_widget):
    selected_index = listbox_widget.curselection()
    if len(selected_index) == 0:
        messagebox.showwarning("警告", "请选择一条记录")
        return
    
    item = listbox_widget.get(selected_index)
    band_id = item.split('|')[0].strip()
    
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        # 删除 fanbands 表中的记录
        cur.execute("""
            DELETE FROM fanbands 
            WHERE FanID=%s AND BandID=%s
        """, (fan_id, band_id))
        db.commit()
        messagebox.showinfo("成功", "删除成功")
        
        # 刷新列表框内容
        refresh_fanbands_listbox()
    except pymysql.Error as e:
        messagebox.showerror("错误", f"删除失败: {str(e)}")
        db.rollback()
    finally:
        db.close()

#刷新喜欢的乐队界面
def refresh_fanbands_listbox():
    fanbands_listbox.delete(0, END)
    rels = get_fanbands_info()
    for idx, rel in enumerate(rels, start=1):
        fanbands_listbox.insert(END, f"{rel[0]} | {rel[1]} | {rel[2]} | {rel[3]} | {rel[4]}")
        fanbands_listbox.update()

#喜欢的乐队界面
def show_fanbands_window(win):
    global fanbands_listbox  # 定义为全局变量
    root = Toplevel(win)
    root.title("歌迷喜欢的乐队信息")
    root.geometry("600x500")

    listbox_frame = Frame(root)
    listbox_frame.pack(side="top", fill="both", expand=True)

    title_label = Label(listbox_frame, text="乐队 ID   乐队名称     队长 ID       成立年份     乐队介绍", font=("Arial", 12), anchor='w')
    title_label.pack(side="top", fill="x")

    scrollbar = Scrollbar(listbox_frame)
    scrollbar.pack(side="right", fill="y")

    fanbands_listbox = Listbox(listbox_frame, selectmode=SINGLE, yscrollcommand=scrollbar.set)
    fanbands_listbox.pack(side="left", fill="both", expand=True)
    scrollbar.config(command=fanbands_listbox.yview)

    rels = get_fanbands_info()
    for idx, rel in enumerate(rels, start=1):
        fanbands_listbox.insert(END, f"{rel[0]} | {rel[1]} | {rel[2]} | {rel[3]} | {rel[4]}")
        fanbands_listbox.update()

    btn_add = Button(root, text="增加喜欢的乐队", command=lambda: add_favorite_band(root, fan_id))
    btn_add.pack(side="left", padx=10, pady=10)

    btn_delete = Button(root, text="删除喜欢的乐队",
                        command=lambda: delete_favorite_band(fanbands_listbox))
    btn_delete.pack(side="right", padx=10, pady=10)

#刷新喜欢的专辑界面
def refresh_fanalbums_listbox():
    # 清空当前列表框内容
    fanbands_listbox.delete(0, END)
    # 重新获取数据并插入列表框
    rels = get_fanalbums_info()
    for idx, rel in enumerate(rels, start=1):
        fanbands_listbox.insert(END, f"{rel[0]} | {rel[2]} | {rel[3]} | {rel[4]}")
        fanbands_listbox.update()

#增加喜欢的专辑
def add_favorite_album(parent_window, fan_id):   # 注意：这里需要传入 fan_id 参数
    def submit():
        selected_album_id = album_id_var.get()
        if not selected_album_id.strip():   # 检查是否选择了有效的专辑 ID
            messagebox.showwarning("警告", "请选择一个专辑")
            return
        
        try:
            db = connect_db(user,password)
            cur = db.cursor()

            # 检查是否存在重复关系
            cur.execute("""
                SELECT COUNT(*) FROM fanalbums 
                WHERE FanID=%s AND AlbumID=%s
            """, (fan_id, selected_album_id))
            count = cur.fetchone()[0]
            if count > 0:
                messagebox.showwarning("提示", "已经存在该条喜欢的专辑记录")
                return
            
            # 插入新记录到 fanalbums 表
            cur.execute("""
                INSERT INTO fanalbums (FanID, AlbumID) VALUES (%s, %s)
            """, (fan_id, selected_album_id))
            db.commit()
            messagebox.showinfo("成功", "添加成功")
            popup.destroy()
            refresh_fanalbums_listbox()
        except pymysql.Error as e:
            messagebox.showerror("错误", f"添加失败 : {str(e)}")
            db.rollback()
        finally:
            db.close()
    
    # 创建弹出窗口
    popup = Toplevel(parent_window)
    popup.title("增加喜欢的专辑")
    popup.geometry("400x200")
    Label(popup, text="请选择您想添加的专辑").pack(pady=10)
    album_id_var = StringVar()

    # 直接获取所有专辑的 AlbumID 并用于组合框的选项
    all_album_ids = [str(album[0]) for album in get_all_albums()]
    album_combobox = Combobox(popup, values=all_album_ids, textvariable=album_id_var)
    album_combobox.pack(pady=10)

    btn_submit = Button(popup, text="提交", command=submit)
    btn_submit.pack(pady=10)
    popup.wait_window()   # 等待窗口关闭

#删除喜欢的专辑
def delete_favorite_album(listbox_widget):
    selected_index = listbox_widget.curselection()
    if len(selected_index) == 0:
        messagebox.showwarning("警告", "请选择一条记录")
        return
    
    item = listbox_widget.get(selected_index)
    album_id = item.split('|')[0].strip()

    try:
        db = connect_db(user,password)
        cur = db.cursor()

        # Delete from fanalbums table
        cur.execute("""
            DELETE FROM fanalbums 
            WHERE FanID=%s AND AlbumID=%s
        """, (fan_id, album_id))

        db.commit()
        messagebox.showinfo("成功", "删除成功")
        
        # Refresh the listbox content after deletion
        listbox_widget.delete(selected_index)
    except pymysql.Error as e:
        messagebox.showerror("错误", f"删除失败: {str(e)}")
        db.rollback()
    finally:
        db.close()

#获取歌迷喜欢的专辑
def get_fanalbums_info():
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "SELECT albums.* FROM albums JOIN fanalbums ON albums.AlbumID=fanalbums.AlbumID WHERE fanalbums.FanID=%s"
        cur.execute(sql, (fan_id,))
        results = cur.fetchall()
        return results
    except pymysql.Error as e:
        print("数据查询失败 " + str(e))
    finally:
        db.close()

#喜欢的专辑界面
def show_fanalbums_window(win):
    global fanbands_listbox  # 定义为全局变量
    root = Toplevel(win)
    root.title("歌迷喜欢的专辑信息")
    root.geometry("600x500")
    
    listbox_frame = Frame(root)
    listbox_frame.pack(side="top", fill="both", expand=True)
    
    title_label = Label(listbox_frame, text="专辑 ID       专辑名称               发布日期           专辑介绍", font=("Arial", 12), anchor='w')
    title_label.pack(side="top", fill="x")
    
    scrollbar = Scrollbar(listbox_frame)
    scrollbar.pack(side="right", fill="y")
    
    fanbands_listbox = Listbox(listbox_frame, selectmode=SINGLE, yscrollcommand=scrollbar.set)
    fanbands_listbox.pack(side="left", fill="both", expand=True)
    scrollbar.config(command=fanbands_listbox.yview)
    
    rels = get_fanalbums_info()
    for idx, rel in enumerate(rels, start=1):
        fanbands_listbox.insert(END, f"{rel[0]} | {rel[2]} | {rel[3]} | {rel[4]}")
        fanbands_listbox.update()
    
    btn_add = Button(root, text="增加喜欢的专辑", command=lambda: add_favorite_album(root, fan_id))
    btn_add.pack(side="left", padx=10, pady=10)
    
    btn_delete = Button(root, text="删除喜欢的专辑",
                        command=lambda: delete_favorite_album(fanbands_listbox))
    btn_delete.pack(side="right", padx=10, pady=10)

#获取歌迷喜欢的歌曲
def get_fansongs_info():
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "SELECT songs.* FROM songs JOIN fansongs ON songs.SongID=fansongs.SongID WHERE fansongs.FanID=%s"
        cur.execute(sql, (fan_id,))
        results = cur.fetchall()
        return results
    except pymysql.Error as e:
        print("数据查询失败 " + str(e))
    finally:
        db.close()

#刷新喜欢的歌曲界面
def refresh_fansongs_listbox(fan_id, listbox_widget):
    listbox_widget.delete(0, END)  # 清空列表框
    rels = get_fansongs_info()
    for rel in rels:
        listbox_widget.insert(END, f"{rel[0]} | {rel[1]} | {rel[2]} | {rel[3]} | {rel[4]}")
        listbox_widget.update()

#增加喜欢的歌曲
def add_favorite_song(parent_window, fan_id):
    def submit():
        selected_song_id = song_id_var.get()
        if not selected_song_id.strip():  # 检查是否选择了有效的歌曲 ID
            messagebox.showwarning("警告", "请选择一首歌曲")
            return
        
        try:
            db = connect_db(user,password)  
            cur = db.cursor()
            # 检查是否存在重复关系
            cur.execute("""
                SELECT COUNT(*) FROM fansongs 
                WHERE FanID=%s AND SongID=%s
            """, (fan_id, selected_song_id))
            count = cur.fetchone()[0]
            if count > 0:
                messagebox.showwarning("提示", "已经存在该条喜欢的歌曲记录")
                return
            
            # 插入新记录到 fansongs 表
            cur.execute("""
                INSERT INTO fansongs (FanID, SongID) VALUES (%s, %s)
            """, (fan_id, selected_song_id))
            db.commit()
            messagebox.showinfo("成功", "添加成功")
            popup.destroy()
            refresh_fansongs_listbox(fan_id, fansongs_listbox)  # 刷新列表框
        except pymysql.Error as e:
            messagebox.showerror("错误", f"添加失败: {str(e)}")
            db.rollback()
        finally:
            db.close()

    # 创建弹出窗口
    popup = Toplevel(parent_window)
    popup.title("增加喜欢的歌曲")
    popup.geometry("400x200")
    Label(popup, text="请选择您想添加的歌曲").pack(pady=10)
    song_id_var = StringVar()
    
    # 直接获取所有歌曲的 SongID 并用于组合框的选项
    all_song_ids = [str(song[0]) for song in get_all_songs()]
    song_combobox = Combobox(popup, values=all_song_ids, textvariable=song_id_var)
    song_combobox.pack(pady=10)
    btn_submit = Button(popup, text="提交", command=submit)
    btn_submit.pack(pady=10)
    popup.wait_window()  # 等待窗口关闭

#删除喜欢的歌曲
def delete_favorite_song(listbox_widget, fan_id):
    selected_index = listbox_widget.curselection()
    if len(selected_index) == 0:
        messagebox.showwarning("警告", "请选择一条记录")
        return
    
    item = listbox_widget.get(selected_index)
    song_id = item.split('|')[0].strip()
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        # 删除从 fansongs 表
        cur.execute("""
            DELETE FROM fansongs 
            WHERE FanID=%s AND SongID=%s
        """, (fan_id, song_id))
        db.commit()
        messagebox.showinfo("成功", "删除成功")
        
        # 刷新列表框内容
        refresh_fansongs_listbox(fan_id, listbox_widget)
    except pymysql.Error as e:
        messagebox.showerror("错误", f"删除失败: {str(e)}")
        db.rollback()
    finally:
        db.close()

#歌迷喜欢的歌曲界面
def show_fansongs_window(win):
    global fansongs_listbox  # 定义为全局变量
    root = Toplevel(win)
    root.title("歌迷喜欢的歌曲信息")
    root.geometry("600x500")

    listbox_frame = Frame(root)
    listbox_frame.pack(side="top", fill="both", expand=True)

    title_label = Label(listbox_frame, text="歌曲 ID       歌曲名称       作曲家       作词家       所属专辑", font=("Arial", 12), anchor='w')
    title_label.pack(side="top", fill="x")

    scrollbar = Scrollbar(listbox_frame)
    scrollbar.pack(side="right", fill="y")

    fansongs_listbox = Listbox(listbox_frame, selectmode=SINGLE, yscrollcommand=scrollbar.set)
    fansongs_listbox.pack(side="left", fill="both", expand=True)
    scrollbar.config(command=fansongs_listbox.yview)

    rels = get_fansongs_info()
    for rel in rels:
        fansongs_listbox.insert(END, f"{rel[0]} | {rel[1]} | {rel[2]} | {rel[3]} | {rel[4]}")
        fansongs_listbox.update()

    btn_add = Button(root, text="增加喜欢的歌曲", command=lambda: add_favorite_song(root, fan_id))
    btn_add.pack(side="left", padx=10, pady=10)

    btn_delete = Button(root, text="删除喜欢的歌曲",
                        command=lambda: delete_favorite_song(fansongs_listbox, fan_id))
    btn_delete.pack(side="right", padx=10, pady=10)

#获取歌迷参加的演唱会
def get_fanconcerts_info():
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "SELECT concerts.* FROM concerts JOIN fanconcerts ON concerts.ConcertID=fanconcerts.ConcertID WHERE fanconcerts.FanID=%s"
        cur.execute(sql, (fan_id,))
        results = cur.fetchall()
        return results
    except pymysql.Error as e:
        print("数据查询失败 " + str(e))
    finally:
        db.close()

#增加参加的演唱会
def add_favorite_concert(parent_window, fan_id):
    def submit():
        selected_concert_id = concert_id_var.get()
        if not selected_concert_id.strip():
            messagebox.showwarning("警告", "请选择一个演唱会")
            return
        
        try:
            db = connect_db(user,password)
            cur = db.cursor()
            
            # Check for duplicate entry
            cur.execute("""
                SELECT COUNT(*) FROM fanconcerts 
                WHERE FanID=%s AND ConcertID=%s
            """, (fan_id, selected_concert_id))
            count = cur.fetchone()[0]
            if count > 0:
                messagebox.showwarning("提示", "已经存在该条参加的演唱会记录")
                return
            
            # Insert into fanconcerts table
            cur.execute("""
                INSERT INTO fanconcerts (FanID, ConcertID) VALUES (%s, %s)
            """, (fan_id, selected_concert_id))
            db.commit()
            messagebox.showinfo("成功", "添加成功")
            popup.destroy()
            refresh_concerts_list()
        except pymysql.Error as e:
            messagebox.showerror("错误", f"添加失败: {str(e)}")
            db.rollback()
        finally:
            db.close()

    # Create popup window
    popup = Toplevel(parent_window)
    popup.title("增加参加的演唱会")
    popup.geometry("400x200")
    
    Label(popup, text="请选择您想添加的演唱会").pack(pady=10)
    concert_id_var = StringVar()
    
    # Get all concert IDs for combobox options
    all_concert_ids = [str(concert[0]) for concert in get_all_concerts()]
    concert_combobox = Combobox(popup, values=all_concert_ids, textvariable=concert_id_var)
    concert_combobox.pack(pady=10)
    
    btn_submit = Button(popup, text="提交", command=submit)
    btn_submit.pack(pady=10)
    
    popup.wait_window()  # Wait for the window to be closed

#删除参加的演唱会
def delete_favorite_concert(listbox_widget):
    selected_index = listbox_widget.curselection()
    if len(selected_index) == 0:
        messagebox.showwarning("警告", "请选择一条记录")
        return
    
    item = listbox_widget.get(selected_index)
    concert_id = item.split('|')[0].strip()
    
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        
        # Delete from fanconcerts table
        cur.execute("""
            DELETE FROM fanconcerts 
            WHERE FanID=%s AND ConcertID=%s
        """, (fan_id, concert_id))
        db.commit()
        messagebox.showinfo("成功", "删除成功")
        
        # Refresh the listbox content after deletion
        refresh_concerts_list()
    except pymysql.Error as e:
        messagebox.showerror("错误", f"删除失败: {str(e)}")
        db.rollback()
    finally:
        db.close()

#刷新参加的演唱会页面
def refresh_concerts_list():
    fanconcerts_listbox.delete(0, END)
    rels = get_fanconcerts_info()
    for idx, rel in enumerate(rels, start=1):
        fanconcerts_listbox.insert(END, f"{rel[0]} | {rel[2]} | {rel[3]} | {rel[4]}")
        fanconcerts_listbox.update()

#参加的演唱会页面
def show_fanconcerts_window(win):
    global fanconcerts_listbox  # Define as global variable
    
    root = Toplevel(win)
    root.title("歌迷参加的演唱会信息")
    root.geometry("600x500")
    
    listbox_frame = Frame(root)
    listbox_frame.pack(side="top", fill="both", expand=True)
    
    title_label = Label(listbox_frame, text="演唱会 ID       演唱会名称             举办日期          地点", font=("Arial", 12), anchor='w')
    title_label.pack(side="top", fill="x")
    
    scrollbar = Scrollbar(listbox_frame)
    scrollbar.pack(side="right", fill="y")
    
    fanconcerts_listbox = Listbox(listbox_frame, selectmode=SINGLE, yscrollcommand=scrollbar.set)
    fanconcerts_listbox.pack(side="left", fill="both", expand=True)
    scrollbar.config(command=fanconcerts_listbox.yview)
    
    rels = get_fanconcerts_info()
    for idx, rel in enumerate(rels, start=1):
        fanconcerts_listbox.insert(END, f"{rel[0]} | {rel[2]} | {rel[3]} | {rel[4]}")
        fanconcerts_listbox.update()
    
    btn_add = Button(root, text="增加参加的演唱会", command=lambda: add_favorite_concert(root, fan_id))
    btn_add.pack(side="left", padx=10, pady=10)
    
    btn_delete = Button(root, text="删除参加的演唱会", command=lambda: delete_favorite_concert(fanconcerts_listbox))
    btn_delete.pack(side="right", padx=10, pady=10)

    root.protocol("WM_DELETE_WINDOW", lambda: (root.destroy(), refresh_concerts_list()))

# 增加评论
def add_review(album_id, review_text, rating):
    try:
        db = connect_db(user,password)
        cur = db.cursor()

        # 检查专辑是否存在
        cur.execute("SELECT AlbumID FROM albums WHERE AlbumID=%s", (album_id,))
        if not cur.fetchone():
            messagebox.showerror("错误", "无效的专辑编号")
            return

        # 插入评论
        insert_sql = """
            INSERT INTO reviews (AlbumID, FanID, ReviewText, Rating)
            VALUES (%s, %s, %s, %s)
        """
        cur.execute(insert_sql, (album_id, fan_id, review_text, rating))
        db.commit()
        messagebox.showinfo("成功", "评论成功")

    except pymysql.Error as e:
        messagebox.showerror("错误", f"评论失败: {str(e)}")
        db.rollback()
    finally:
        db.close()


# 显示乐评窗口
def comment_album_win(win):
    comment_win = Toplevel(win)
    comment_win.title("给专辑评论")
    comment_win.geometry("400x300")

    # 获取所有专辑ID及其名称供选择
    album_options = get_album_names_with_ids()
    
    album_id_combobox = Combobox(comment_win, values=[f"{aid} - {name}" for aid, name in album_options], width=40)
    album_id_combobox.set("请选择专辑名")
    album_id_combobox.pack(pady=10)

    review_text_entry = Text(comment_win, height=5, width=40)
    review_text_entry.pack(pady=10)

    rating_spinbox = Spinbox(comment_win, from_=0.0, to=5.0, increment=0.5, width=5)
    rating_spinbox.pack(pady=10)

    submit_button = Button(comment_win, text="提交",
                           command=lambda: add_review(
                               extract_album_id(album_id_combobox.get()),
                               review_text_entry.get("1.0", END).strip(),
                               float_safe(rating_spinbox.get())
                           ))
    submit_button.pack(pady=10)


# 获取专辑ID和名称
def get_album_names_with_ids():
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        cur.execute("SELECT AlbumID, AlbumName FROM albums")
        return cur.fetchall()
    except pymysql.Error as e:
        print(f"获取专辑信息失败: {str(e)}")
        return []
    finally:
        db.close()


# 提取专辑ID
def extract_album_id(combobox_value):
    try:
        return combobox_value.split('-')[0].strip()
    except IndexError:
        messagebox.showerror("错误", "请选择有效的专辑")
        return None


# 将字符串安全转换为浮点数
def float_safe(value):
    try:
        return float(value)
    except ValueError:
        messagebox.showerror("错误", "请输入有效的评分")
        return None
'''
#增加评论
def add_review(album_id, review_text, rating):
    try:
        db = connect_db('Jiayi','12345')
        cur = db.cursor()
        
        # 提交前先获取当前最大ReviewID
        cur.execute("SELECT MAX(ReviewID) FROM reviews")
        max_review_id = cur.fetchone()[0] or 0
        
        sql = """
            INSERT INTO reviews (AlbumID, FanID, ReviewText, Rating, ReviewID)
            VALUES (%s, %s, %s, %s, %s)
        """
        cur.execute(sql, (album_id, fan_id, review_text, rating, max_review_id + 1))  # 添加ReviewID

        db.commit()
        messagebox.showinfo("成功", "评论成功")
    except pymysql.Error as e:
        messagebox.showerror("错误", f"评论失败: {str(e)}")
        db.rollback()
    else:
        # 如果没有异常，更新最大ReviewID并保存到数据库
        update_max_review_id(db, max_review_id + 1)
    finally:
        db.close()

#最大乐评序号
def update_max_review_id(db, new_review_id):
    cursor = db.cursor()
    cursor.execute("UPDATE reviews SET ReviewID = %s WHERE ReviewID = %s", (new_review_id, new_review_id - 1))
    db.commit()

#显示乐评窗口
def comment_album_win(win):
    comment_win = Toplevel(win)
    comment_win.title("给专辑评论")
    comment_win.geometry("400x300")

    album_id_combobox = Combobox(comment_win, values=get_album_ids(), width=30)
    album_id_combobox.set("请选择专辑名")
    album_id_combobox.pack(pady=10)

    review_text_entry = Text(comment_win, height=5, width=40)
    review_text_entry.pack(pady=10)

    rating_spinbox = Spinbox(comment_win, from_=0.0, to=5.0, increment=0.5, width=5)
    rating_spinbox.pack(pady=10)

    submit_button = Button(comment_win, text="提交",
                       command=lambda: add_review(album_id_combobox.get(),
                                                 review_text_entry.get("1.0", END),
                                                 float_safe(rating_spinbox.get())))

    def float_safe(value):
        try:
            return float(value)
        except ValueError:
            messagebox.showerror("错误", "请输入有效的评分")
        return None
    submit_button.pack(pady=10)

#获取专辑号
def get_album_ids():
    try:
        db = connect_db('Jiayi','12345')
        cur = db.cursor()
        cur.execute("SELECT AlbumName FROM albums")
        results = [row[0] for row in cur.fetchall()]
        return results
    except pymysql.Error as e:
        print(f"获取专辑ID失败: {str(e)}")
        return []
    finally:
        db.close()
'''
# 获取自己的乐评信息
def get_fanreviews_info():
    try:
        db = connect_db('RockHeart','11111')
        cur = db.cursor()
        sql = "SELECT reviews.*,fans.Name,albums.AlbumName FROM reviews JOIN albums ON reviews.AlbumID=albums.AlbumID JOIN fans ON fans.FanID=reviews.FanID WHERE reviews.FanID=%s"
        cur.execute(sql, (fan_id,))
        results = cur.fetchall()
        return results
    except pymysql.Error as e:
        print("数据查询失败 " + str(e))
    finally:
        db.close()

# 显示自己乐评信息
def show_fanreviews_window(win):
    root = Toplevel(win)
    root.title("乐队专辑乐评信息")
    root.geometry("600x500")
    Label(root, text="乐评ID         专辑名                评论                                              评分").grid(row=0, column=0, sticky="w", columnspan=2, padx=5, pady=9)
    rels = get_fanreviews_info()
    i = 1
    for rel in rels:
        s1 = "{:<14}{:<14}{:<20}{:<14}".format(
            rel[0], rel[6], rel[3], rel[4])
        Label(root, text=s1).grid(row=i, column=0, columnspan=2, padx=5, pady=9, sticky="w")
        i += 1

# 获取所有乐队信息
def get_all_fbands():
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "SELECT * FROM bands"
        cur.execute(sql)
        results = cur.fetchall()
        return results
    except pymysql.Error as e:
        print("数据查询失败 " + str(e))
    finally:
        db.close()

# 获取所有成员信息
def get_all_fmembers():
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "SELECT * FROM members"
        cur.execute(sql)
        results = cur.fetchall()
        return results
    except pymysql.Error as e:
        print("数据查询失败 " + str(e))
    finally:
        db.close()

#获取所有歌曲信息
def get_all_fsongs():
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "SELECT songs.*,albums.AlbumName FROM songs JOIN albums ON songs.AlbumID=albums.AlbumID"
        cur.execute(sql)
        results = cur.fetchall()
        return results
    except pymysql.Error as e:
        print("数据查询失败 " + str(e))
    finally:
        db.close()

# 获取所有专辑信息
def get_all_falbums():
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "SELECT * FROM albums"
        cur.execute(sql)
        results = cur.fetchall()
        return results
    except pymysql.Error as e:
        print("数据查询失败 " + str(e))
    finally:
        db.close()

# 获取所有演唱会信息
def get_all_fconcerts():
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "SELECT concerts.*,bands.BandName FROM concerts JOIN bands on bands.BandID=concerts.ConcertID"
        cur.execute(sql)
        results = cur.fetchall()
        return results
    except pymysql.Error as e:
        print("数据查询失败 " + str(e))
    finally:
        db.close()

# 查看所有乐队信息
def show_all_fbands(win):
    root = Toplevel(win)
    root.title("所有乐队信息")
    root.geometry("600x500")
    Label(root, text="乐队ID          乐队名称          队长ID             成立年份      乐队介绍").grid(row=0, column=0, columnspan=2, padx=5, pady=9,  sticky="w")
    rels = get_all_fbands()
    i = 1
    for rel in rels:
        s1 = "{:<14}{:<14}{:<14}{:<14}{:<14}".format(rel[0], rel[1], rel[2], rel[3], rel[4])
        Label(root, text=s1).grid(row=i, column=0, columnspan=2, padx=5, pady=9, sticky="w")
        i += 1

# 查看所有成员信息
def show_all_fmembers(win):
    root = Toplevel(win)
    root.title("所有成员信息")
    root.geometry("600x500")
    Label(root, text="成员ID       乐队ID         姓名           性别            年龄           角色           加入日期           离开日期").grid(row=0, column=0, columnspan=2, padx=5, pady=9, sticky="w")
    rels = get_all_fmembers()
    i = 1
    for rel in rels:
        s1 = "{:<14}{:<14}{:<14}{:<14}{:<14}{:<14}{:<14}{:<14}".format(rel[0], rel[1], rel[2], rel[3], rel[4], rel[5], rel[6], rel[7] if rel[7] else "")
        Label(root, text=s1).grid(row=i, column=0, columnspan=2, padx=5, pady=9, sticky="w")
        i += 1

#查看所有歌曲信息
def show_all_fsongs(win):
    root = Toplevel(win)
    root.title("所有歌曲信息")
    root.geometry("600x500")
    Label(root, text="歌曲ID           歌曲名称           作曲家           作词家           所属专辑").grid(row=0, column=0, sticky="w",columnspan=2, padx=5, pady=9)
    rels = get_all_fsongs()
    i = 1
    for rel in rels:
        s1 = "{:<14}{:<14}{:<14}{:<14}{:<14}".format(rel[0], rel[1], rel[2], rel[3], rel[5])
        Label(root, text=s1).grid(row=i, column=0, columnspan=2, padx=5, pady=9, sticky="w")
        i += 1

# 查看所有专辑信息
def show_all_falbums(win):
    root = Toplevel(win)
    root.title("所有专辑信息")
    root.geometry("600x500")
    Label(root, text="专辑ID           专辑名称                  发布日期                           专辑介绍").grid(row=0, column=0, sticky="w",columnspan=2, padx=5, pady=9)
    rels = get_all_falbums()
    i = 1
    for rel in rels:
        s1 = "{:<14}{:<14}{:<14}{:<14}".format(rel[0], rel[2], rel[3], rel[4])
        Label(root, text=s1).grid(row=i, column=0, columnspan=2, padx=5, pady=9, sticky="w")
        i += 1

# 查看所有演唱会信息
def show_all_fconcerts(win):
    root = Toplevel(win)
    root.title("所有演唱会信息")
    root.geometry("600x500")
    Label(root, text="演唱会ID         乐队名         演唱会名称            举办日期         地点").grid(row=0, column=0, columnspan=2, padx=5, pady=9, sticky="w")
    rels = get_all_fconcerts()
    i = 1
    for rel in rels:
        s1 = "{:<14}{:<14}{:<14}{:<14}{:<14}".format(rel[0], rel[5], rel[2], rel[3], rel[4])
        Label(root, text=s1).grid(row=i, column=0, columnspan=2, padx=5, pady=9, sticky="w")
        i += 1

#-------------管理员操作函数------------
# 获取所有乐队信息
def get_all_bands():
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "SELECT * FROM bands"
        cur.execute(sql)
        results = cur.fetchall()
        return results
    except pymysql.Error as e:
        print("数据查询失败 " + str(e))
    finally:
        db.close()

# 获取所有成员信息
def get_all_members():
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "SELECT * FROM members"
        cur.execute(sql)
        results = cur.fetchall()
        return results
    except pymysql.Error as e:
        print("数据查询失败 " + str(e))
    finally:
        db.close()

#获取所有歌曲信息
def get_all_songs():
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "SELECT songs.*,albums.AlbumName FROM songs JOIN albums ON songs.AlbumID=albums.AlbumID"
        cur.execute(sql)
        results = cur.fetchall()
        return results
    except pymysql.Error as e:
        print("数据查询失败 " + str(e))
    finally:
        db.close()

# 获取所有专辑信息
def get_all_albums():
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "SELECT * FROM albums"
        cur.execute(sql)
        results = cur.fetchall()
        return results
    except pymysql.Error as e:
        print("数据查询失败 " + str(e))
    finally:
        db.close()

# 获取所有演唱会信息
def get_all_concerts():
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "SELECT concerts.*,bands.BandName FROM concerts JOIN bands on bands.BandID=concerts.ConcertID"
        cur.execute(sql)
        results = cur.fetchall()
        return results
    except pymysql.Error as e:
        print("数据查询失败 " + str(e))
    finally:
        db.close()

# 获取所有歌迷信息
def get_all_fans():
    try:
        db = connect_db(user,password)
        cur = db.cursor()
        sql = "SELECT * FROM fans"
        cur.execute(sql)
        results = cur.fetchall()
        return results
    except pymysql.Error as e:
        print("数据查询失败 " + str(e))
    finally:
        db.close()

# 查看所有乐队信息
def show_all_bands(win):
    root = Toplevel(win)
    root.title("所有乐队信息")
    root.geometry("600x500")
    Label(root, text="乐队ID          乐队名称          队长ID             成立年份      乐队介绍").grid(row=0, column=0, columnspan=2, padx=5, pady=9,  sticky="w")
    rels = get_all_bands()
    i = 1
    for rel in rels:
        s1 = "{:<14}{:<14}{:<14}{:<14}{:<14}".format(rel[0], rel[1], rel[2], rel[3], rel[4])
        Label(root, text=s1).grid(row=i, column=0, columnspan=2, padx=5, pady=9, sticky="w")
        i += 1

# 查看所有成员信息
def show_all_members(win):
    root = Toplevel(win)
    root.title("所有成员信息")
    root.geometry("600x500")
    Label(root, text="成员ID       乐队ID         姓名           性别            年龄           角色           加入日期           离开日期").grid(row=0, column=0, columnspan=2, padx=5, pady=9, sticky="w")
    rels = get_all_members()
    i = 1
    for rel in rels:
        s1 = "{:<14}{:<14}{:<14}{:<14}{:<14}{:<14}{:<14}{:<14}".format(rel[0], rel[1], rel[2], rel[3], rel[4], rel[5], rel[6], rel[7] if rel[7] else "")
        Label(root, text=s1).grid(row=i, column=0, columnspan=2, padx=5, pady=9, sticky="w")
        i += 1

#查看所有歌曲信息
def show_all_songs(win):
    root = Toplevel(win)
    root.title("所有歌曲信息")
    root.geometry("600x500")
    Label(root, text="歌曲ID           歌曲名称           作曲家           作词家           所属专辑").grid(row=0, column=0, sticky="w",columnspan=2, padx=5, pady=9)
    rels = get_all_songs()
    i = 1
    for rel in rels:
        s1 = "{:<14}{:<14}{:<14}{:<14}{:<14}".format(rel[0], rel[1], rel[2], rel[3], rel[5])
        Label(root, text=s1).grid(row=i, column=0, columnspan=2, padx=5, pady=9, sticky="w")
        i += 1

# 查看所有专辑信息
def show_all_albums(win):
    root = Toplevel(win)
    root.title("所有专辑信息")
    root.geometry("600x500")
    Label(root, text="专辑ID           专辑名称                  发布日期                           专辑介绍").grid(row=0, column=0, sticky="w",columnspan=2, padx=5, pady=9)
    rels = get_all_albums()
    i = 1
    for rel in rels:
        s1 = "{:<14}{:<14}{:<14}{:<14}".format(rel[0], rel[2], rel[3], rel[4])
        Label(root, text=s1).grid(row=i, column=0, columnspan=2, padx=5, pady=9, sticky="w")
        i += 1

# 查看所有演唱会信息
def show_all_concerts(win):
    root = Toplevel(win)
    root.title("所有演唱会信息")
    root.geometry("600x500")
    Label(root, text="演唱会ID         乐队名         演唱会名称            举办日期         地点").grid(row=0, column=0, columnspan=2, padx=5, pady=9, sticky="w")
    rels = get_all_concerts()
    i = 1
    for rel in rels:
        s1 = "{:<14}{:<14}{:<14}{:<14}{:<14}".format(rel[0], rel[5], rel[2], rel[3], rel[4])
        Label(root, text=s1).grid(row=i, column=0, columnspan=2, padx=5, pady=9, sticky="w")
        i += 1

# 查看所有歌迷信息
def show_all_fans(win):
    root = Toplevel(win)
    root.title("所有歌迷信息")
    root.geometry("600x500")
    Label(root, text="歌迷ID         姓名           性别            年龄         职业            学历").grid(row=0, column=0, columnspan=2, padx=5, pady=9, sticky="w")
    rels = get_all_fans()
    i = 1
    for rel in rels:
        s1 = "{:<14}{:<14}{:<14}{:<14}{:<14}{:<14}".format(rel[0], rel[1], rel[2], rel[3], rel[4], rel[5])
        Label(root, text=s1).grid(row=i, column=0, columnspan=2, padx=5, pady=9, sticky="w")
        i += 1

#-------------修改密码操作函数------------
# 修改管理员密码
def change_admin_password(win, table):
    change_pwd = Toplevel(win)
    change_pwd.title("修改密码")
    change_pwd.geometry("350x200")
    new_pwdE = Entry(change_pwd, width=30)
    confirm_pwdE = Entry(change_pwd, width=30)
    Label(change_pwd, text="修改密码", font="微软雅黑 14").grid(row=0, column=0, columnspan=2, sticky="w", pady=10)
    Label(change_pwd, text="新密码", font="微软雅黑 14").grid(row=1, column=0, sticky="w")
    new_pwdE.grid(row=1, column=1, sticky="e", padx=40)
    Label(change_pwd, text="确认密码", font="微软雅黑 14").grid(row=2, column=0, sticky="w")
    confirm_pwdE.grid(row=2, column=1, sticky="e", padx=40)
    Button(change_pwd, text="修改", font="微软雅黑 10", relief="solid", command=lambda: update_admin_sql(table, new_pwdE.get(), confirm_pwdE.get())).grid(row=3, column=0, columnspan=2, pady=20, padx=20)

# 更新管理员密码
def update_admin_sql(table, pwd1, pwd2):
    if pwd1 == "":
        messagebox.showwarning("错误", "请不要输入空值")
    elif pwd2 != pwd1:
        messagebox.showwarning("错误", "上下密码不相等")
    else:
        try:
            db = connect_db("Admin1","123456")
            cur = db.cursor()
            sql = f"UPDATE {table} SET Pwd=%s WHERE {table.split('login')[0]}ID=%s"
            if table == 'bandlogin':
                cur.execute(sql, (pwd1, band_id))
            elif table == 'fanlogin':
                cur.execute(sql, (pwd1, fan_id))
            else:
                cur.execute(sql, (pwd1, admin_id))
            db.commit()
            messagebox.showinfo("成功", "修改成功")
        except pymysql.Error as e:
            print(e)
            messagebox.showinfo("失败", "修改失败")
            db.rollback()
        finally:
            db.close()

# 修改乐队密码
def change_band_password(win, table):
    change_pwd = Toplevel(win)
    change_pwd.title("修改密码")
    change_pwd.geometry("350x200")
    new_pwdE = Entry(change_pwd, width=30)
    confirm_pwdE = Entry(change_pwd, width=30)
    Label(change_pwd, text="修改密码", font="微软雅黑 14").grid(row=0, column=0, columnspan=2, sticky="w", pady=10)
    Label(change_pwd, text="新密码", font="微软雅黑 14").grid(row=1, column=0, sticky="w")
    new_pwdE.grid(row=1, column=1, sticky="e", padx=40)
    Label(change_pwd, text="确认密码", font="微软雅黑 14").grid(row=2, column=0, sticky="w")
    confirm_pwdE.grid(row=2, column=1, sticky="e", padx=40)
    Button(change_pwd, text="修改", font="微软雅黑 10", relief="solid", command=lambda: update_band_sql(table, new_pwdE.get(), confirm_pwdE.get())).grid(row=3, column=0, columnspan=2, pady=20, padx=20)

# 更新乐队密码
def update_band_sql(table, pwd1, pwd2):
    if pwd1 == "":
        messagebox.showwarning("错误", "请不要输入空值")
    elif pwd2 != pwd1:
        messagebox.showwarning("错误", "上下密码不相等")
    else:
        try:
            db = connect_db("RockHeart","11111")
            cur = db.cursor()
            sql = f"UPDATE {table} SET Pwd=%s WHERE {table.split('login')[0]}ID=%s"
            if table == 'bandlogin':
                cur.execute(sql, (pwd1, band_id))
            elif table == 'fanlogin':
                cur.execute(sql, (pwd1, fan_id))
            else:
                cur.execute(sql, (pwd1, admin_id))
            db.commit()
            messagebox.showinfo("成功", "修改成功")
        except pymysql.Error as e:
            print(e)
            messagebox.showinfo("失败", "修改失败")
            db.rollback()
        finally:
            db.close()

# 修改乐迷密码
def change_fan_password(win, table):
    change_pwd = Toplevel(win)
    change_pwd.title("修改密码")
    change_pwd.geometry("350x200")
    new_pwdE = Entry(change_pwd, width=30)
    confirm_pwdE = Entry(change_pwd, width=30)
    Label(change_pwd, text="修改密码", font="微软雅黑 14").grid(row=0, column=0, columnspan=2, sticky="w", pady=10)
    Label(change_pwd, text="新密码", font="微软雅黑 14").grid(row=1, column=0, sticky="w")
    new_pwdE.grid(row=1, column=1, sticky="e", padx=40)
    Label(change_pwd, text="确认密码", font="微软雅黑 14").grid(row=2, column=0, sticky="w")
    confirm_pwdE.grid(row=2, column=1, sticky="e", padx=40)
    Button(change_pwd, text="修改", font="微软雅黑 10", relief="solid", command=lambda: update_fan_sql(table, new_pwdE.get(), confirm_pwdE.get())).grid(row=3, column=0, columnspan=2, pady=20, padx=20)

# 更新乐迷密码
def update_fan_sql(table, pwd1, pwd2):
    if pwd1 == "":
        messagebox.showwarning("错误", "请不要输入空值")
    elif pwd2 != pwd1:
        messagebox.showwarning("错误", "上下密码不相等")
    else:
        try:
            db = connect_db("Jiayi","12345")
            cur = db.cursor()
            sql = f"UPDATE {table} SET Pwd=%s WHERE {table.split('login')[0]}ID=%s"
            if table == 'bandlogin':
                cur.execute(sql, (pwd1, band_id))
            elif table == 'fanlogin':
                cur.execute(sql, (pwd1, fan_id))
            else:
                cur.execute(sql, (pwd1, admin_id))
            db.commit()
            messagebox.showinfo("成功", "修改成功")
        except pymysql.Error as e:
            print(e)
            messagebox.showinfo("失败", "修改失败")
            db.rollback()
        finally:
            db.close()


#-------------三个操作界面和公共主界面------------
# 管理员用户操作界面
def admin_operate():
    admin_log = Tk()
    admin_log.title("管理员操作台")
    admin_log.geometry("310x310")
    Label(admin_log, text="Hello, " + admin_name + " \n 请选择您的操作 \n ", font="微软雅黑 14", justify=LEFT).grid(row=0, column=0, columnspan=2, sticky='w')
    
    Button(admin_log, text="查看所有乐队", font="微软雅黑 12", command=lambda: show_all_bands(admin_log)).grid(row=1, column=0, sticky="w")
    Button(admin_log, text="查看所有成员", font="微软雅黑 12", command=lambda: show_all_members(admin_log)).grid(row=1, column=1,sticky="w", padx=50)
    Button(admin_log, text="查看所有歌曲", font="微软雅黑 12", command=lambda: show_all_songs(admin_log)).grid(row=2, column=0, sticky="w")
    Button(admin_log, text="查看所有专辑", font="微软雅黑 12", command=lambda: show_all_albums(admin_log)).grid(row=2, column=1, sticky="w", padx=50)
    Button(admin_log, text="查看所有演唱会", font="微软雅黑 12", command=lambda: show_all_concerts(admin_log)).grid(row=3, column=0, sticky="w")
    Button(admin_log, text="查看所有歌迷", font=" 微软雅黑 12", command=lambda: show_all_fans(admin_log)).grid(row=3, column=1,sticky="w", padx=50)
    Button(admin_log, text="修改密码", font="微软雅黑 12", command=lambda: change_admin_password(admin_log, "adminlogin")).grid(row=4, column=0, sticky="w")

# 乐队用户操作界面
def band_operate():
    band_log = Tk()
    band_log.title("乐队操作台")
    band_log.geometry("310x310")

    Label(band_log, text="Hello, " + band_name + "\n 请选择您的操作 \n ", font="微软雅黑 14", justify=LEFT).grid(row=0, column=0, columnspan=2, sticky='w')
    Button(band_log, text="乐队信息", font="微软雅黑 12", command=lambda: update_band_window(band_log)).grid(row=1, column=0, sticky="w")
    Button(band_log, text="乐队成员", font="微软雅黑 12", command=lambda: update_members_window(band_log)).grid(row=1, column=1, padx=50)
    Button(band_log, text="乐队专辑", font="微软雅黑 12", command=lambda: update_albums_window(band_log)).grid(row=2, column=0, sticky="w", pady=10)
    Button(band_log, text="乐队演唱会", font="微软雅黑 12", command=lambda: update_concerts_window(band_log)).grid(row=2, column=1, padx=50)
    Button(band_log, text="乐队歌迷", font="微软雅黑 12", command=lambda: show_fans_window(band_log)).grid(row=3, column=0, sticky="w")
    Button(band_log, text="乐队乐评", font="微软雅黑 12", command=lambda: show_reviews_window(band_log)).grid(row=3, column=1, padx=50, pady=10)
    Button(band_log, text="修改密码", font="微软雅黑 12", command=lambda: change_band_password(band_log, "bandlogin")).grid(row=4, column=0, pady=10)

# 歌迷用户操作界面
def fan_operate():
    fan_log = Tk()
    fan_log.title("歌迷操作台")
    fan_log.geometry("400x440")

    Label(fan_log, text="Hello, " + fan_name + "\n 请选择您的操作 \n ", font="微软雅黑 14", justify=LEFT).grid(row=0, column=0, columnspan=2, sticky='w')
    Button(fan_log, text="更新乐迷信息", font="微软雅黑 12",command=lambda: update_fan_window(fan_log)).grid(row=1, column=0, sticky="w")
    Button(fan_log, text="查看喜欢的乐队", font="微软雅黑 12",command=lambda: show_fanbands_window(fan_log)).grid(row=1, column=1, sticky="w", padx=50)
    Button(fan_log, text="查看喜欢的专辑", font="微软雅黑 12",command=lambda: show_fanalbums_window(fan_log)).grid(row=2, column=0,sticky="w")
    Button(fan_log, text="查看喜欢的歌曲", font="微软雅黑 12",command=lambda: show_fansongs_window(fan_log)).grid(row=2, column=1, sticky="w", padx=50, pady=10)
    Button(fan_log, text="查看参加的演唱会", font="微软雅黑 12", command=lambda: show_fanconcerts_window(fan_log)).grid(row=3, column=0, sticky="w")
    Button(fan_log, text="查看自己的乐评信息", font="微软雅黑 12", command=lambda: show_fanreviews_window(fan_log)).grid(row=3, column=1, padx=50, sticky="w")
    Button(fan_log, text="给专辑评论", font="微软雅黑 12",command=lambda: comment_album_win(fan_log)).grid(row=4, column=0,pady=10,sticky="w")
    Button(fan_log, text="修改密码", font="微软雅黑 12", command=lambda: change_fan_password(fan_log, "fanlogin")).grid(row=4, column=1,padx=50, sticky="w")
    Button(fan_log, text="查看所有乐队", font="微软雅黑 12",command=lambda: show_all_fbands(fan_log)).grid(row=6, column=0, sticky="w")
    Button(fan_log, text="查看所有成员", font="微软雅黑 12",command=lambda: show_all_fmembers(fan_log)).grid(row=6, column=1, sticky="w", padx=50)
    Button(fan_log, text="查看所有歌曲", font="微软雅黑 12",command=lambda: show_all_fsongs(fan_log)).grid(row=7, column=0,sticky="w")
    Button(fan_log, text="查看所有专辑", font="微软雅黑 12",command=lambda: show_all_falbums(fan_log)).grid(row=7, column=1, sticky="w", padx=50, pady=10)
    Button(fan_log, text="查看所有演唱会", font="微软雅黑 12", command=lambda: show_all_fconcerts(fan_log)).grid(row=8, column=0, sticky="w")

# 主界面
band_id = ""
band_name = ""
fan_id = ""
fan_name = ""
admin_id = ""
admin_name = ""
master = Tk()
master.title("欢迎")
master.geometry("450x370+500+200")
canvas = Canvas(master, height=130, width=440)
image3 = PhotoImage(file="welcome.gif")
canvas.create_image(0, 0, anchor='nw', image=image3)
canvas.grid(row=0, column=0, columnspan=2)
Label(text="你好 \n 请选择你的登录方式 : \n", font="微软雅黑 14", justify=LEFT).grid(row=1, column=0, columnspan=2, sticky='w', pady=10)
Button(master, text="乐队", font="微软雅黑 14", relief="solid", command=lambda: login("bandlogin", master)).grid(sticky='w', row=3, column=0, pady=10)
Button(master, text="歌迷", font="微软雅黑 14", relief="solid", command=lambda: login("fanlogin", master)).grid(sticky='e', row=3, column=1)
Button(master, text="管理员", font="微软雅黑 14", relief="solid", command=lambda: login("adminlogin", master)).grid(sticky='s', row=4, column=0, columnspan=2, pady=10)
master.mainloop()